package com.yalu.util;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.mysql.jdbc.StringUtils;


public class ExcelUtil {
	public static Row getRow(Sheet sheet, int rowIdx) {
		Row row = sheet.getRow(rowIdx);
		if (row == null) {
			row = sheet.createRow(rowIdx);
		}
		return row;
	}
	
	public static Cell getCell(Row row, int cellIdx) {
		Cell cell = row.getCell(cellIdx);
		if (cell == null) {
			cell = row.createCell(cellIdx);
		}
		cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
		return cell;
	}
	
	public static void setCellValue(Row row, int cellIdx, String value) {
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value);
	}
	
	
	public static void setCellValue(Row row, int cellIdx, BigDecimal value, int scale) {
		if (value == null) {
			return;
		}
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value.setScale(scale, RoundingMode.HALF_UP).doubleValue());
	}
	
	public static void setCellValue(Row row, int cellIdx, Long value) {
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value);
	}

    public static String getCellValue(final Row row, final int col, final boolean isDate) {
        Cell cell = row.getCell(col);
        if (cell == null) {
            return "";
        }

        int cellType = cell.getCellType();
        String value = null;
        try {
            switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getStringCellValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (isDate) {
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                        value = format.format(cell.getDateCellValue());
                    } else {
                        value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
                    }
                default:
                    break;
            }
        } catch (Exception e) {
            value = cell.getStringCellValue();
        }

        return StringUtils.isNullOrEmpty(value) ? "" : value.trim().replace("'", "''");
    }
}
